package org.bgee.psp4jdbc.jdbcapi;

import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.Map;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.bgee.psp4jdbc.Properties;
import org.bgee.psp4jdbc.pool.Pool;
import org.bgee.psp4jdbc.pool.ReleaseException;

/**
 * The aim of this class is to wrap a real <code>java.sql.Connection</code> and 
 * to hold a <code>Pool</code> of {@link PreparedStatement}s. It returns 
 * {@link PreparedStatement}s and {@link CallableStatement}s wrapping real 
 * <code>java.sql.PreparedStatement</code>s and <code>java.sql.CallableStatement</code>s, 
 * and that return to the pool and <code>close</code> is called, rather than being 
 * actually closed.
 * 
 * @author Mathieu Seppey
 * @author Frederic Bastian
 * @version 1, August 2013
 * @see PreparedStatement
 * @see CallableStatement
 * @since 1
 *
 */
public class Connection implements java.sql.Connection
{

    /**
     * <code>Logger</code> of the class. 
     */
    private final static Logger log = LogManager.getLogger(Connection.class.getName());

    /**
     * The real <code>Connection</code> that this class wraps. 
     */
    private java.sql.Connection realConnection;

    /**
     * A <code>Pool</code> which contains every already existing and available
     * <code>PreparedStatement</code> provided by this <code>Connection</code>.
     * Its maximal size can be defined in <code>Properties</code>. 
     * <p>
     * When a <code>PreparedStatement</code> is being used, it is removed 
     * from this pool. It is returned to this pool when <code>close</code> 
     * is called on it. The real underlying <code>PreparedStatement</code> 
     * is not actually closed, but rather, its parameters are reset by calling 
     * <code>clearParameters</code> on it. 
     * 
     * @see Pool
     * @see Properties
     */
    private final Pool pool;

    /**
     * Default constructor, should not be used. 
     * Constructor private, so that only a {@link DataSource} or a {@link Driver}
     * can provide a <code>Connection</code>.
     */
    // Constructor not public, so that only a DataSource/Driver can provide a Connection.
    // suppress warnings
    @SuppressWarnings("unused")
    private Connection()
    {
        this(null, null);
    }

    /**
     * Constructor used to instantiate this connection.
     * 
     * Constructor protected, so that only a {@link Driver} or a {@link DataSource}
     * can instantiate a <code>Connection</code>.
     * 
     * @param realConnection    The <code>java.sql.Connection</code> that this class wraps.
     * @param properties		The <code>Properties</code> to set parameters of 
     * 							this <code>Connection</code> instance.
     */
    protected Connection(java.sql.Connection realConnection, Properties properties)
    {
        log.entry(realConnection, properties);
        
        this.realConnection = realConnection;
        this.pool = new Pool(properties.getPoolMaxSize(), properties.getEvictionMethod(), 
        		properties.getEvictionFactor());
        
        log.exit();
    }
    
    protected Pool getPool() {
    	return this.pool;
    }

    @Override
    public void close() throws SQLException
    {
        try {
			this.getPool().close();
		} catch (ReleaseException e) {
			throw log.throwing(new SQLException(e));
		}
        this.realConnection.close();
    }
    
    public java.sql.Connection getRealConnection() {
    	return this.realConnection;
    }

    /** 
     * All prepareStatement(...) and prepareCall(...) method are redirected to this method
     * to deal with the <code>PreparedStatement</code> <code>Pool</code>
     * <p>
     * The <code>PreparedStatement</code> is fetched in the <code>Pool</code> if 
     * any is available for the requested SQL and parameters.
     * Else, a new one is created and returned.
     * <p>
     * When an existing <code>PreparedStatement</code> is returned, it is
     * removed from the <code>Pool</code>.
     * <p>
     * It hashes the SQL and other parameters to use it as <code>Pool</code> key.
     * Two <code>PreparedStatement</code> created with the same SQL but different
     * parameters will be pooled under a different key to ensure that the user request
     * is totally matched by the returned <code>PreparedStatement</code>.
     * <p>
     * See <code>Connection.prepareStatement</code> and <code>Connection.prepareCall</code>
     * public methods for parameters details
     * 
     * @param sql 					a <code>String</code> with contains the 
     * 									PreparedStatement SQL
     * @param resultSetType 		an <code>Integer</code>
     * @param resultSetConcurrency 	an <code>Integer</code>
     * @param resultSetHoldability 	an <code>Integer</code>
     * @param columnIndexes 		an <code>Integer[]</code>
     * @param columnNames 			a <code>String[]</code>
     * @param autoGeneratedKeys 	an <code>Integer</code>
     * @param realMethodToCall 		an <code>int</code> to quickly identify the real method
     *          					to call on the real <code>Connection</code>.
     *          <ul>
     *          <li>0 : prepareStatement(SQL)
     *          <li>1 : prepareStatement(SQL,resultSetType)
     *          <li>2 : prepareStatement(SQL,resultSetType,resultSetConcurrency
     *                  ,resultSetHoldability)
     *          <li>3 : prepareStatement(SQL,columnIndexes)
     *          <li>4 : prepareStatement(SQL,columnNames)
     *          <li>5 : prepareStatement(SQL,autoGeneratedKeys)
     *          <li>6 : prepareCall(SQL)
     *          <li>7 : prepareCall(SQL,resultSetType)
     *          <li>8 : prepareCall(SQL,resultSetType,resultSetConcurrency
     *                  ,resultSetHoldability)
     *          </ul>
     * 
     * @return  a <code>PreparedStatement</code> already existing and retrieved from 
     * 			the pool hold by this <code>Connection</code>, or a newly created one 
     * 			otherwise.
     *  
     * @throws SQLException if a database error occurred or if the method is called on
     * a closed connection
     */
    private PreparedStatement prepareCallOrStatement(String sql, Integer resultSetType,
            Integer resultSetConcurrency, Integer resultSetHoldability, 
            int[] columnIndexes,String[]  columnNames,Integer autoGeneratedKeys,
            int realMethodToCall) throws SQLException 
    {

        log.entry(sql, resultSetType, resultSetConcurrency, resultSetHoldability, 
        		columnIndexes, columnNames, autoGeneratedKeys, realMethodToCall);

        // The key is made of the SQL + all other params + the int used to identify the
        // real method to call. It ensures that the user request is totally satisfied by
        // the object returned by the pool. And as it is not likely that an application
        // will send its SQL request through many different use of prepareXXX method,
        // the number of pooled elements will not explode because of that.
        //
        String key = sql + resultSetType + resultSetConcurrency + resultSetHoldability + 
        		     columnIndexes + columnNames + autoGeneratedKeys + realMethodToCall;
        String digestedKey = DigestUtils.sha256Hex(key) ;

        // If the statement is already in the pool, return it
        PreparedStatement ps =  (PreparedStatement) this.getPool().remove(digestedKey);
        if (ps != null && !ps.getRealStatement().isClosed()) {
            log.debug("Return an already existing PreparedStatement : {}", ps);  
            return log.exit(ps);
        }

        // Else, create the preparedStatement from the real connection
        switch(realMethodToCall){

        case 1:
            ps = new PreparedStatement(digestedKey, this.getPool(), this,
                    this.realConnection.prepareStatement(sql,resultSetType,
                            resultSetConcurrency));
            break;

        case 2:
            ps = new PreparedStatement(digestedKey, this.getPool(), this,
                    this.realConnection.prepareStatement(sql,resultSetType,
                            resultSetConcurrency,resultSetHoldability));
            break;

        case 3:
            ps = new PreparedStatement(digestedKey, this.getPool(), this,
                    this.realConnection.prepareStatement(sql,columnIndexes));
            break;

        case 4:
            ps = new PreparedStatement(digestedKey, this.getPool(), this,
                    this.realConnection.prepareStatement(sql,columnNames));
            break;

        case 5:
            ps = new PreparedStatement(digestedKey, this.getPool(), this,
                    this.realConnection.prepareStatement(sql,autoGeneratedKeys));
            break;


        case 6:
            ps = new CallableStatement(digestedKey, this.getPool(), this,
                    this.realConnection.prepareCall(sql));
            break;


        case 7:
            ps = new CallableStatement(digestedKey, this.getPool(), this,
                    this.realConnection.prepareCall(sql,resultSetType,
                            resultSetConcurrency));;
                            break;


        case 8:
            ps = new CallableStatement(digestedKey, this.getPool(), this,
                    this.realConnection.prepareCall(sql,resultSetType,
                            resultSetConcurrency,resultSetHoldability));
            break;    

        case 9:    
        default :   
            ps = new PreparedStatement(digestedKey, this.getPool(), this,
                    this.realConnection.prepareStatement(sql));

        }
        
        log.debug("Return a newly created PreparedStatement : {}", ps);
        return log.exit(ps);

    }

    /***
     * Redefinition of all prepareXXXX method to send them to the private method
     * prepareCallOrStatement
     */
    @Override
    public PreparedStatement prepareStatement(String sql)
            throws SQLException
    {
        return prepareCallOrStatement(sql, null, null, null, null, null, null, 0);
    }
    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType,
            int resultSetConcurrency) throws SQLException
    {
        return prepareCallOrStatement(sql, resultSetType, resultSetConcurrency, null, 
        		null, null, null, 1);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType,
            int resultSetConcurrency, int resultSetHoldability) throws SQLException
    {
        return prepareCallOrStatement(sql, resultSetType,resultSetConcurrency, 
        		resultSetHoldability, null, null, null, 2);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
            throws SQLException
    {
        return prepareCallOrStatement(sql, null, null, null, columnIndexes, 
        		null, null, 3);
    }
    @Override
    public PreparedStatement prepareStatement(String sql, String[] columnNames)
            throws SQLException
    {
        return prepareCallOrStatement(sql, null, null, null, null, columnNames, null, 4);
    }
    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
            throws SQLException
    {
        return prepareCallOrStatement(sql, null, null, null, null, null, 
        		autoGeneratedKeys, 5);
    } 

    @Override
    public CallableStatement prepareCall(String sql)
            throws SQLException
    {
        return (CallableStatement) prepareCallOrStatement(
                sql, null, null, null, null, null, null, 6);
    }
    @Override
    public CallableStatement prepareCall(String sql, int resultSetType,
            int resultSetConcurrency)
                    throws SQLException
    {
        return (CallableStatement) prepareCallOrStatement(
                sql, resultSetType, resultSetConcurrency, null, null, null, null, 7);
    }
    @Override
    public CallableStatement prepareCall(String sql, int resultSetType,
            int resultSetConcurrency, int resultSetHoldability)
                    throws SQLException
    {
        return (CallableStatement) prepareCallOrStatement(
                sql, resultSetType, resultSetConcurrency, resultSetHoldability,
                null, null, null, 8);
    }

    //***********************************
    // Methods that are simply delegated.
    //***********************************

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException
    {
        return realConnection.unwrap(iface);
    }

    @Override
    public int hashCode()
    {
        return realConnection.hashCode();
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException
    {
        return realConnection.isWrapperFor(iface);
    }

    @Override
    public Statement createStatement() throws SQLException
    {
        return realConnection.createStatement();
    }

    @Override
    public boolean equals(Object obj)
    {
        return realConnection.equals(obj);
    }

    @Override
    public String nativeSQL(String sql) throws SQLException
    {
        return realConnection.nativeSQL(sql);
    }

    @Override
    public void setAutoCommit(boolean autoCommit) throws SQLException
    {
        realConnection.setAutoCommit(autoCommit);
    }

    @Override
    public String toString()
    {
        return realConnection.toString();
    }

    @Override
    public boolean getAutoCommit() throws SQLException
    {
        return realConnection.getAutoCommit();
    }

    @Override
    public void commit() throws SQLException
    {
        realConnection.commit();
    }

    @Override
    public void rollback() throws SQLException
    {
        realConnection.rollback();
    }

    @Override
    public boolean isClosed() throws SQLException
    {
        return realConnection.isClosed();
    }

    @Override
    public DatabaseMetaData getMetaData() throws SQLException
    {
        return realConnection.getMetaData();
    }

    @Override
    public void setReadOnly(boolean readOnly) throws SQLException
    {
        realConnection.setReadOnly(readOnly);
    }

    @Override
    public boolean isReadOnly() throws SQLException
    {
        return realConnection.isReadOnly();
    }

    @Override
    public void setCatalog(String catalog) throws SQLException
    {
        realConnection.setCatalog(catalog);
    }

    @Override
    public String getCatalog() throws SQLException
    {
        return realConnection.getCatalog();
    }

    @Override
    public void setTransactionIsolation(int level) throws SQLException
    {
        realConnection.setTransactionIsolation(level);
    }

    @Override
    public int getTransactionIsolation() throws SQLException
    {
        return realConnection.getTransactionIsolation();
    }

    @Override
    public SQLWarning getWarnings() throws SQLException
    {
        return realConnection.getWarnings();
    }

    @Override
    public void clearWarnings() throws SQLException
    {
        realConnection.clearWarnings();
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency)
            throws SQLException
            {
        return realConnection.createStatement(resultSetType,
                resultSetConcurrency);
            }

    @Override
    public Map<String, Class<?>> getTypeMap() throws SQLException
    {
        return realConnection.getTypeMap();
    }

    @Override
    public void setTypeMap(Map<String, Class<?>> map) throws SQLException
    {
        realConnection.setTypeMap(map);
    }

    @Override
    public void setHoldability(int holdability) throws SQLException
    {
        realConnection.setHoldability(holdability);
    }

    @Override
    public int getHoldability() throws SQLException
    {
        return realConnection.getHoldability();
    }

    @Override
    public Savepoint setSavepoint() throws SQLException
    {
        return realConnection.setSavepoint();
    }

    @Override
    public Savepoint setSavepoint(String name) throws SQLException
    {
        return realConnection.setSavepoint(name);
    }

    @Override
    public void rollback(Savepoint savepoint) throws SQLException
    {
        realConnection.rollback(savepoint);
    }

    @Override
    public void releaseSavepoint(Savepoint savepoint) throws SQLException
    {
        realConnection.releaseSavepoint(savepoint);
    }

    @Override
    public Statement createStatement(int resultSetType,
            int resultSetConcurrency, int resultSetHoldability)
                    throws SQLException
                    {
        return realConnection.createStatement(resultSetType,
                resultSetConcurrency, resultSetHoldability);
                    }

    @Override
    public Clob createClob() throws SQLException
    {
        return realConnection.createClob();
    }

    @Override
    public Blob createBlob() throws SQLException
    {
        return realConnection.createBlob();
    }

    @Override
    public NClob createNClob() throws SQLException
    {
        return realConnection.createNClob();
    }

    @Override
    public SQLXML createSQLXML() throws SQLException
    {
        return realConnection.createSQLXML();
    }

    @Override
    public boolean isValid(int timeout) throws SQLException
    {
        return realConnection.isValid(timeout);
    }

    @Override
    public void setClientInfo(String name, String value)
            throws SQLClientInfoException
            {
        realConnection.setClientInfo(name, value);
            }

    @Override
    public void setClientInfo(java.util.Properties properties)
            throws SQLClientInfoException
            {
        realConnection.setClientInfo(properties);
            }

    @Override
    public String getClientInfo(String name) throws SQLException
    {
        return realConnection.getClientInfo(name);
    }

    @Override
    public java.util.Properties getClientInfo() throws SQLException
    {
        return realConnection.getClientInfo();
    }

    @Override
    public Array createArrayOf(String typeName, Object[] elements)
            throws SQLException
            {
        return realConnection.createArrayOf(typeName, elements);
            }

    @Override
    public Struct createStruct(String typeName, Object[] attributes)
            throws SQLException
            {
        return realConnection.createStruct(typeName, attributes);
            }
}
